%%%-------------------------------------------------------------------
%%% @author liuwentao
%%% @doc
%%%     数据库连接进程
%%% @end
%%% Created : 17. 6月 2021 14:52
%%%-------------------------------------------------------------------
-module(sql_conn_mgr).
-author("liuwentao").

-behaviour(gen_server).


%% 进程接口
-export([
    start_link/0        %% 进程开启函数
    , exec/1
    , exec/2
    , select_limit/3
    , select_limit/4
    , get_one/1
    , get_one/2
    , get_row/1
    , get_row/2
    , get_all/1
    , get_all/2
    , get_all/3
    , insert/1
    , insert/2
    , insert_vals_sql/1
    , format_sql/2
    , t/1
]).


%% gen_server callbacks
-export([init/1, handle_call/3, handle_cast/2, handle_info/2, terminate/2, code_change/3]).

-define(SERVER, ?MODULE).
-include("common.hrl").
-include("emysql.hrl").
-include("role.hrl").

-record(state, {}).

%%%===================================================================
%%% API
%%%===================================================================

%% @doc 数据库连接初始化（创建连接池）
-spec sql_init() -> ok.
sql_init() ->
    [DbHost, DbPort, DbUser, DbPass, DbName, DbEncode, DbConnNumMin, DbConnNumMax] = env:get(db_cfg),
    case emysql:add_pool(?DB, DbConnNumMin, DbConnNumMax, DbUser, DbPass, DbHost, DbPort, DbName, DbEncode) of
        ok -> ?INFO("mysql连接池建立成功");
        {error, pool_already_exists} ->
            ?ERR("mysql连接池建立失败")
    end.

%% @doc 执行一条数据库语句，注意：不能用这个接口执行查询类的语句
-spec exec(bitstring() | string()) -> ok | {error, term()}.
exec(Sql) ->
    case catch emysql:execute(?DB, Sql) of
        #ok_packet{} -> ok;
        #error_packet{msg = Msg} -> {error, Msg};
        Err -> {error, Err}
    end.

%% @doc 执行一条数据库语句，带参数，注意：不能用这个接口执行查询类的语句
-spec exec(bitstring() | string(), [term()]) -> ok | {error, term()}.
exec(Sql, Args) ->
    case catch emysql:execute(?DB, Sql, Args) of
        #ok_packet{} -> ok;
        #error_packet{msg = Msg} -> {error, Msg};
        Err -> {error, Err}
    end.

%% @doc 专门用于插入一条数据并返回最新的自增id
insert(Sql) ->
    case catch emysql:execute(?DB, Sql) of
        #ok_packet{insert_id = NewId} -> {ok, NewId};
        #error_packet{msg = Msg} -> {error, Msg};
        Err -> {error, Err}
    end.

%% @doc 专门用于插入一条数据并返回最新的自增id
insert(Sql, Args) ->
    case catch emysql:execute(?DB, Sql, Args) of
        #ok_packet{insert_id = NewId} -> {ok, NewId};
        #error_packet{msg = Msg} -> {error, Msg};
        Err -> {error, Err}
    end.

%% @doc 执行分页查询，返回结果中的所有行
-spec select_limit(bitstring() | string(), non_neg_integer(), pos_integer()) ->
    {ok, [term()]} | {error, term()}.
select_limit(Sql, Offset, Num) ->
    S = list_to_binary([Sql, <<" limit ">>, integer_to_list(Offset), <<", ">>, integer_to_list(Num)]),
    case catch emysql:execute(?DB, S) of
        #result_packet{rows = Rows} -> {ok, Rows};
        #error_packet{msg = Msg} -> {error, Msg};
        Err -> {error, Err}
    end.

%% @doc 执行分页查询(带格式化参数)，返回结果中的所有行
-spec select_limit(bitstring() | string(), [term()], non_neg_integer(), pos_integer()) ->
    {ok, [term()]} | {error, term()}.
select_limit(Sql, Args, Offset, Num) ->
    S = list_to_binary([Sql, " limit ", integer_to_list(Offset), ", ", integer_to_list(Num)]),
    case catch emysql:execute(?DB, S, Args) of
        #result_packet{rows = Rows} -> {ok, Rows};
        #error_packet{msg = Msg} -> {error, Msg};
        Err -> {error, Err}
    end.

%% @doc 取出查询结果中的第一行第一列(不带格式化参数)
%% <div>注意：必须确保返回结果中不会多于一行一列，其它情况或未找到时返回{error, undefined}</div>
-spec get_one(bitstring() | string()) -> {ok, term()} | {error, term()}.
get_one(Sql) ->
    case catch emysql:execute(?DB, Sql) of
        #result_packet{rows = []} -> {ok, undefined};
        #result_packet{rows = [[R]]} -> {ok, R};
        #error_packet{msg = Msg} -> {error, Msg};
        Err -> {error, Err}
    end.

%% @doc 取出查询结果中的第一行第一列(带有格式化参数)
%% <div>注意：必须确保返回结果中不会多于一行一列，其它情况或未找到时返回{error, undefined}</div>
-spec get_one(bitstring() | string(), [term()]) -> {ok, term()} | {error, term()}.
get_one(Sql, Args) ->
    case catch emysql:execute(?DB, Sql, Args) of
        #result_packet{rows = [[R]]} -> {ok, R};
        #result_packet{rows = []} -> {ok, undefined};
        #error_packet{msg = Msg} -> {error, Msg};
        Err -> {error, Err}
    end.

%% @doc 取出查询结果中的第一行
%% <div>注意：必须确保返回结果中不会多于一行，其它情况或未找到时返回{ok, undefined}</div>
-spec get_row(bitstring() | string()) -> {ok, [term()]} | {error, term()}.
get_row(Sql) ->
    case catch emysql:execute(?DB, Sql) of
        #result_packet{rows = [R]} -> {ok, R};
        #result_packet{rows = []} -> {ok, undefined};
        #error_packet{msg = Msg} -> {error, Msg};
        Err -> {error, Err}
    end.

%% @doc 取出查询结果中的第一行(带有格式化参数)
%% <div>注意：必须确保返回结果中不会多于一行，其它情况或未找到时返回{ok, undefined}</div>
-spec get_row(bitstring() | string(), [term()]) -> {ok, [term()]} | {error, term()}.
get_row(Sql, Args) ->
    case catch emysql:execute(?DB, Sql, Args) of
        #result_packet{rows = [R]} -> {ok, R};
        #result_packet{rows = []} -> {ok, undefined};
        #error_packet{msg = Msg} -> {error, Msg};
        Err -> {error, Err}
    end.

%% @doc 取出查询结果中的所有行
-spec get_all(bitstring() | string()) -> {ok, term()} | {error, term()}.
get_all(Sql) ->
    case catch emysql:execute(?DB, Sql) of
        #result_packet{rows = R} -> {ok, R};
        #error_packet{msg = Msg} -> {error, Msg};
        Err -> {error, Err}
    end.

%% @doc 取出查询结果中的所有行
-spec get_all(bitstring() | string(), [term()]) -> {ok, term()} | {error, term()}.
get_all(Sql, Args) ->
    case catch emysql:execute(?DB, Sql, Args) of
        #result_packet{rows = R} -> {ok, R};
        #error_packet{msg = Msg} -> {error, Msg};
        Err -> {error, Err}
    end.

%% @doc 取出查询结果中的所有行
-spec get_all(bitstring() | string(), [term()], integer()) -> {ok, term()} | {error, term()}.
get_all(Sql, Args, Timeout) ->
    case catch emysql:execute(?DB, Sql, Args, Timeout) of
        #result_packet{rows = R} -> {ok, R};
        #error_packet{msg = Msg} -> {error, Msg};
        Err -> {error, Err}
    end.

%% @doc 格式化SQL语句
-spec format_sql(Sql :: bitstring(), Args :: list()) -> bitstring().
format_sql(Sql, Args) ->
    NewArgs = [emysql_util:encode(A) || A <- Args],
    iolist_to_binary(io_lib:format(Sql, NewArgs)).

%% @doc 创建插入语句值部分
-spec insert_vals_sql(Fields :: list()) -> string().
insert_vals_sql(Fields) ->
    Sql = lists:concat(["(", string:join(lists:duplicate(length(Fields), "~s"), ","), ")"]),
    binary_to_list(format_sql(Sql, Fields)).


%% @doc 进程启动
-spec(start_link() ->
    {ok, Pid :: pid()} | ignore | {error, Reason :: term()}).
start_link() ->
    gen_server:start_link({local, ?SERVER}, ?MODULE, [], []).

%%%===================================================================
%%% gen_server callbacks
%%%===================================================================
init([]) ->
    sql_init(),
    {ok, #state{}}.


handle_call(_Request, _From, State) ->
    {reply, ok, State}.

handle_cast(_Request, State) ->
    {noreply, State}.

handle_info(_Info, State) ->
    {noreply, State}.

terminate(_Reason, _State) ->
    ok.

code_change(_OldVsn, State, _Extra) ->
    {ok, State}.

%%%===================================================================
%%% 工具函数
%%%===================================================================


%% ----------------------------------------------------
%% 测试代码
%% ----------------------------------------------------

t(Times) ->
    I = Times,
%%    F1 = fun(I) ->
        S = integer_to_list(I),
        A = "account_" ++ S,
        N = "name_" ++ S,
        R = #role{id = I, platform = "4399", zone_id = 0, account = A, name = N},
        Sql = "insert into role (platform, zone_id, account, name, ver, data) values (?, ?, ?, ?, ?, ?)",
        emysql:execute(?DB, Sql, ["4399", 0, A, N, 1, util:term_to_string(R)]).
%%         end,
%%    lists:foreach(F1, lists:seq(1, Times)).
